﻿using System;
using System.Collections.Generic;

using System.Web;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using _WebShared.Classes;
using KPIS.GERP.ESS.MODEL;

namespace KPIS.GERP.ESS.DAL
{
    public class D_SECURITY_Users
    {//string strGHRMS_DB_Name = ConfigurationSettings.AppSettings["GHRMS.DB.Name"].ToString();

        public DataTable GetUserInfo(string strUserID, string PassWord)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Sequence", strUserID);
                dbManager.AddParameters(1, "@PassWord", PassWord);
                dbManager.AddParameters(2, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                strSQL = "SELECT u.*"
                    + ", cast(CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) as char) AS USER_NAME"
                    + ", p.POS_REF, p.POS_NAME, j.JOB_SITE_NAME, w.JOB_SITE_NAME AS WORKING_JOB_SITE_NAME"
                    + ", c.CLASS_NAME, e.WORKING_JOB_SITE_SEQ, w.WORK_AREA_SEQ,e.pos_seq"
                    + " FROM SYS_SC_USERS u"
                    //+ " LEFT JOIN SYS_SC_USER_ROLES ur ON u.USER_SEQ = ur.USER_SEQ"
                    //+ " LEFT JOIN SYS_SC_ROLES r ON ur.ROLE_SEQ = ur.ROLE_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e ON u.EMP_SEQ = e.EMP_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                    + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                    + " LEFT JOIN HRM_MP_JOB_SITES j ON p.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                    + " LEFT JOIN HRM_MP_JOB_SITES w ON e.WORKING_JOB_SITE_SEQ = w.JOB_SITE_SEQ"
                    + " WHERE u.RECORD_STATUS = @RecordStatus"
                    + (string.IsNullOrEmpty(strUserID) ? "" : " AND u.USER_CODE = @Sequence")
                    + (string.IsNullOrEmpty(PassWord) ? "" : " AND u.USER_PASSWORD = @PassWord");
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];


            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            if (dtb.Rows.Count > 0)
            {
                var workingJobSiteLoaded = CheckJobSiteLevel(dtb.Rows[0]["WORKING_JOB_SITE_SEQ"].ToString());
                dtb.Rows[0]["WORKING_JOB_SITE_SEQ"] = workingJobSiteLoaded ?? (object)DBNull.Value ;
            }
            return dtb;
        }

        public DataTable GetUserInfo(int empSeq)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@Sequence", empSeq);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                strSQL = "SELECT u.*"
                    + ", cast(CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) as char) AS USER_NAME"
                    + ", p.POS_REF, p.POS_NAME, j.JOB_SITE_NAME, w.JOB_SITE_NAME AS WORKING_JOB_SITE_NAME"
                    + ", c.CLASS_NAME, e.WORKING_JOB_SITE_SEQ, w.WORK_AREA_SEQ"
                    + " FROM SYS_SC_USERS u"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e ON u.EMP_SEQ = e.EMP_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                    + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                    + " LEFT JOIN HRM_MP_JOB_SITES j ON p.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                    + " LEFT JOIN HRM_MP_JOB_SITES w ON e.WORKING_JOB_SITE_SEQ = w.JOB_SITE_SEQ"
                    + " WHERE u.RECORD_STATUS = @RecordStatus"
                    + " AND e.emp_seq = @Sequence";
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];


            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            if (dtb.Rows.Count > 0)
            {
                var workingJobSiteLoaded = CheckJobSiteLevel(dtb.Rows[0]["WORKING_JOB_SITE_SEQ"].ToString());
                dtb.Rows[0]["WORKING_JOB_SITE_SEQ"] = workingJobSiteLoaded ?? (object)DBNull.Value;
            }
            return dtb;
        }

        public string GetWorkingJobSiteByEmpSeq(string emp_seq)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());

            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                string sql;
                sql = " select working_job_site_seq"
                      + " from hrm_ps_employees"
                      + " where emp_seq = @emp_seq";

                DataTable dt;
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return CheckJobSiteLevel(dt.Rows[0]["working_job_site_seq"].ToString());
            }
            catch (Exception ex)
            {

            }
            finally
            {
                dbManager.Dispose();
            }
            return null;
        }

        public string CheckJobSiteLevel(string job_site_seq)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider,
                ConfigurationSettings.AppSettings["ConnectionString"].ToString());
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@job_site_seq", job_site_seq);
                string sql;

                sql = " select job_site_level_seq, upper_job_site_seq"
                      + " from hrm_mp_job_sites"
                      + " where job_site_seq = @job_site_seq"
                      + " and job_site_level_seq >= 3";

                dbManager.Open();
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];

                if (dtb.Rows.Count > 0)
                {
                    string job_site_level_seq = dtb.Rows[0]["job_site_level_seq"].ToString();

                    if (job_site_level_seq == "3")
                    {
                        return job_site_seq;
                    }
                    else
                    {
                        return CheckJobSiteLevel(dtb.Rows[0]["upper_job_site_seq"].ToString());
                    }
                }

                return null;
            }
            catch
            {
            }
            finally
            {
                dbManager.Dispose();
            }
            return null;
        }

        public DataTable GetMenuItemList(string strUserID, string strSystemID)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@strUserID", strUserID);
                dbManager.AddParameters(2, "@strSystemID", strSystemID);

                if (strSystemID != "ESS")
                {
                strSQL = "SELECT DISTINCT ml.MENU_ITEM_SEQ AS SEQ"
                        + ", CONCAT('<div class=font-menu>', ml.MENU_ITEM_NAME, '</div>') AS NAME"
                        + ", ml.UPPER_MENU_ITEM_SEQ  AS UpperSeq"
                        + ", ml.MENU_ITEM_PATH AS PATH"
                        + ", ml.MENU_ITEM_TARGET AS TARGET"
                            + ", ml.RECORD_STATUS, ml.MENU_ITEM_CODE"
                    + " FROM SYS_SC_USERS u"
                    + " INNER JOIN SYS_SC_USER_ROLES ur ON u.USER_SEQ = ur.USER_SEQ"
                        + " INNER JOIN SYS_SC_ROLES r ON ur.ROLE_SEQ = r.ROLE_SEQ"
                        + " INNER JOIN SYS_SC_PERMISSIONS p ON r.ROLE_SEQ = p.ROLE_SEQ"
                        + " INNER JOIN SYS_MENU_ITEMS_LIST ml ON p.MENU_ITEM_SEQ = ml.MENU_ITEM_SEQ"
                    + " WHERE u.RECORD_STATUS <> @DeleteRecord"
                        + " AND ur.RECORD_STATUS <> @DeleteRecord"
                        + " AND r.RECORD_STATUS <> @DeleteRecord"
                        + " AND p.RECORD_STATUS <> @DeleteRecord"
                        + " AND ml.RECORD_STATUS <> @DeleteRecord"
                        + (strUserID != "" ? " AND u.USER_SEQ = @strUserID" : "")
                        + " AND ml.SYSTEM_ID = @strSystemID"
                    + " ORDER BY ml.MENU_ITEM_CODE";
                }
                else
                {
                    strSQL = "SELECT DISTINCT ml.MENU_ITEM_SEQ AS SEQ"
                            + ", CONCAT('<div class=font-menu>', ml.MENU_ITEM_NAME, '</div>') AS NAME"
                            + ", ml.UPPER_MENU_ITEM_SEQ  AS UpperSeq"
                            + ", ml.MENU_ITEM_PATH AS PATH"
                            + ", ml.MENU_ITEM_TARGET AS TARGET"
                            + ", ml.RECORD_STATUS, ml.MENU_ITEM_CODE"
                        + " FROM SYS_MENU_ITEMS_LIST ml"
                        + " WHERE ml.RECORD_STATUS <> @DeleteRecord"
                            //+ (strUserID != "" ? " AND u.USER_SEQ = @strUserID" : "")
                            + " AND ml.SYSTEM_ID = @strSystemID"
                        + " ORDER BY ml.MENU_ITEM_CODE";
                }
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch ( Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }
        public DataTable GetMenuItemListNew(string strUserID, string strSystemID)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@strUserID", strUserID);
                dbManager.AddParameters(2, "@strSystemID", strSystemID); 
                strSQL = @" 
	 
	SELECT us.user_seq, ro.system 	FROM sys_sc_user_roles ur 
		INNER JOIN sys_sc_users us ON us.user_seq = ur.user_seq 
		INNER JOIN sys_sc_roles ro ON ro.role_seq = ur.role_seq  
	WHERE ur.record_status <> @DeleteRecord
	    AND ro.record_status <> @DeleteRecord
	    AND us.record_status <> @DeleteRecord
	    AND system =  @strSystemID
	    AND us.user_seq = @strUserID";
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }
        public bool CheckPassword(string Sequence, string OldPassword)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        
            string strSQL;
            bool bOK = false;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@OldPassword", OldPassword);

                DataSet ds = null;
                DataRowCollection drc;

                strSQL = "SELECT *"
                    + " FROM SYS_SC_USERS"
                    + " WHERE USER_SEQ = @Sequence"
                        + " AND USER_PASSWORD = @OldPassword";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables.Count > 0)
                {
                    drc = ds.Tables[0].Rows;
                    bOK = drc.Count > 0;
                }                
                return bOK;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

        }
        
        public void ChangePassword(int Sequence, string Password, int UpdatedBy)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@Password", Password);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE SYS_SC_USERS"
                    + " SET USER_PASSWORD = @Password"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE USER_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public bool CheckIsApprover(string emp_seq)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        
            string strSQL;
            bool bOK = false;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@emp_seq", emp_seq);

                DataSet ds = null;
                DataRowCollection drc;

                strSQL = "select m.role_seq"

                    + " from hrm_ps_employees emp"
                    + " left join sys_sc_user_roles ur on emp.pos_seq = ur.pos_seq"
	                    + " and ur.record_status <> 'D'"
                    + " left join sys_sc_permissions m on ur.role_seq = m.role_seq"
	                    + " and m.record_status <> 'D'"

                    + " where emp.emp_seq = @emp_seq"
                    + " and m.permission_type = 'A'";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables.Count > 0)
                {
                    drc = ds.Tables[0].Rows;
                    bOK = drc.Count > 0;
                }
                return bOK;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
